Categorization trees are materialized as a result of queries comprising the metadata attributes and the structured data attributes derived from the electronic form cell values. Together with the ordered and filtered object lists the categorization trees provide means to select subsets of objects. The categorization tree queries may be defined by authorized system users and stored in the knowledge base of the docuRob®Ontology Manager module.

Figure 1 presents the expanded categorization tree Documents grouped by year, month and type created by the Group work process instances.

Figure 1. The expanded categorization tree.

The categorization tree nodes are defined as SQL queries comprising the metadata attributes as well as the structured data attributes stored as the electronic form field values. In the latter case the attributes must be stored as attributes of the relational database table corresponding to the object type.

Figure 2. Level 1 of the categorization tree (Years)

Materialization semantics of the subsequent levels of SQL queries are defined in the following tables corresponding to the respective SQL query levels. The number of query levels and the query level semantics depend on the modelled context of the corresponding categorization tree.

Level 1

This part of the SQL query is to count the number of distinct documents created in each year. Documents that do not comprise the attribute Type_ID are not counted.
Query	Result
SELECT EXTRACT(YEAR FROM D.creation_date) AS ID	The year value is extracted for the creation_date column of the DOCUMENT table (named D) and rewrites it in the ID column.
TO_CHAR(EXTRACT(YEAR FROM D.creation_date), '9999') AS VALUE	The year value is again extracted from the creation_date column of the DOCUMENT table and subsequently converted into 4 digit sequence (i.e. “2023”) and is rewritten in the VALUE column.
count(distinct d.id) AS COUNTER	The number of distinct identifier values for each group of documents (d.id) is stored in the COUNTER column.
FROM DOCUMENT D	The DOCUMENT table name is assigned the D alias.
WHERE D.TYPE_ID IS NOT NULL	All result rows comprising the empty value od the TYPE_ID attribute are filtered out.
GROUP BY EXTRACT(YEAR FROM D.creation_date)	The result rows are grouped by year extracted from creation_date to allow for the function count(distinct d.id) to count the number of the distinct documents.
ORDER BY EXTRACT(YEAR FROM D.creation_date)	The result rows are sorted in the ascending order based on the creation_date attribute values.

Table 1. Level 1 of the categorization tree semantics

The respective levels of the categorization trees are associated within the knowledge graph of the docuRob®Ontology Manager module.

Figure 3. Level 2 of the categorization tree

Level 2

This part of the SQL query is to count the number of distinct documents created in the month of each year. Documents that do not comprise the attribute Type_ID are not counted.
Zapytanie	Wynik
SELECT EXTRACT(MONTH FROM D.creation_date) AS ID	The month value is extracted for the creation_date column of the DOCUMENT table (named D) and rewrites it in the ID column.
TO_CHAR(EXTRACT(MONTH FROM D.creation_date), '09') AS VALUE	The month value is again extracted from the creation_date column of the DOCUMENT table and subsequently converted into 2 digit sequence (i.e. “02”) and is rewritten in the VALUE column.
count(distinct d.id) AS COUNTER	The number of distinct identifier values for each group of documents (d.id) is stored in the COUNTER column
FROM DOCUMENT D	The DOCUMENT table name is assigned the D alias.
WHERE D.TYPE_ID IS NOT NULL	All result rows comprising the empty value TYPE_ID attribute are filtered out.
AND EXTRACT(YEAR FROM D.creation_date) = 'PARAM_1'	Additionally the result rows having the year value D.creation_date=’PARAM_1’ are extracted.
GROUP BY EXTRACT(MONTH FROM D.creation_date)	The result rows are grouped by month to allow for the function count(distinct d.id) to count the number of the distinct documents.

Table 2. Leve2 of the categorization tree semantics

Figure 4. Level 3 of the categorization tree

The metadata attribute type_id must be specified as the dictionary value for all objects types stored in the repository. Additional information regarding the categorization tree definition are available in the documentation of the docuRob®Ontology Manager module.

Level 3

This part of the SQL query is to count the number of distinct documents for each document type (type_id) created in the year and month. Documents that do not comprise the attribute Type_ID are not counted.
Zapytanie	Wynik
SELECT d.type_id AS ID,	The type_id value is extracted from the DOCUMENT table (named D) and is rewritten in the ID column.
d.type_id AS VALUE	The type_id value is again extracted from the DOCUMENT table and is rewritten in the VALUE column.
count(distinct d.id) AS COUNTER	The number of distinct identifier values for each group of documents (d.id) is stored in the COUNTER column
FROM DOCUMENT D	The DOCUMENT table name is assigned the D alias.
WHERE D.TYPE_ID IS NOT NULL	All result rows comprising the empty value TYPE_ID attribute are filtered out.
AND EXTRACT(YEAR FROM D.creation_date) = 'PARAM_1'	Additionally the result rows having the year value D.creation_date=’PARAM_1’ are extracted.
AND EXTRACT(MONTH FROM D.creation_date) = 'PARAM_2'	Additionally the result rows having month value of the D.creation_date=’PARAM_2’ are extracted.
GROUP BY d.type_id	The result rows are grouped by type_id to allow for the function count(distinct d.id) to count the number of the distinct documents.

Table 3. Leve3 of the categorization tree semantics